package com.css.fxfzypg.modules.qhmapfiles.repository.impl;

import com.css.fxfzypg.modules.qhmapfiles.repository.QhMapFilesImport;
import com.css.fxfzypg.modules.qhmapfiles.repository.QhMapFilesNativeRepository;
import com.css.fxfzypg.modules.qhmapfiles.repository.entity.QhMapFilesEntity;
import com.css.fxfzypg.modules.qhmapfiles.viewobjects.QhMapFilesQueryParams;
import com.css.fxfzypg.util.PlatformObjectUtils;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.context.annotation.PropertySource;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;
import java.util.List;

/**
 * @author zyt
 * @date 2022-09-26
 */
@Repository
@PropertySource("classpath:fxfz-config.yml")
public class QhMapFilesNativeRepositoryImpl implements QhMapFilesNativeRepository {
//    @PersistenceContext
//    private EntityManager em;

    private static final Logger log = LoggerFactory.getLogger(QhMapFilesNativeRepositoryImpl.class);

    @Autowired
    private QhMapFilesImport qhMapFilesImport;

    @Resource
    JdbcTemplate jdbcTemplate;


    @Override
    public int searchMapFilesNumByJDBC(QhMapFilesQueryParams queryParams) {
        StringBuffer sql = new StringBuffer("select * from qh_map_files ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            whereSql.append(" and province like '%" + queryParams.getProvince() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            whereSql.append(" and city like '%" + queryParams.getCity() + "%'");
        } else {
            whereSql.append(" and (city IS NULL OR LENGTH(city)=0) ");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
            whereSql.append(" and area like '%" + queryParams.getArea() + "%'");
        } else {
            whereSql.append(" and (area IS NULL OR LENGTH(area)=0) ");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAffiliatedFileName())) {
            whereSql.append(" and affiliated_file_name ='" + queryParams.getAffiliatedFileName() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSave())) {
            whereSql.append(" and is_save ='" + queryParams.getIsSave() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsDelete())) {
            whereSql.append(" and is_delete = '" + queryParams.getIsDelete() + "'");
        }
        //上传类型（行政区划_）
        if (!PlatformObjectUtils.isEmpty(queryParams.getType())) {
            whereSql.append(" and type = '" + queryParams.getType() + "'");
        }
        //上传省市级类型（0：省，1：市）
        if (!PlatformObjectUtils.isEmpty(queryParams.getQualityStatus())) {
            whereSql.append(" and quality_status = '" + queryParams.getQualityStatus() + "'");
        }
        //上传日期
        if (!PlatformObjectUtils.isEmpty(queryParams.getDateStr())) {
            whereSql.append(" and date_str = '" + queryParams.getDateStr() + "'");
        }
        List<QhMapFilesEntity> result = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(QhMapFilesEntity.class));
        //return jdbcTemplate.queryForObject(sql.append(whereSql).toString(), Integer.class);
        return PlatformObjectUtils.isNotEmpty(result) && result.size()>0?result.size():0;
    }
    @Override
    public List<QhMapFilesEntity> searchMapFilesByJDBC(QhMapFilesQueryParams queryParams) {
        //List<QhMapFilesEntity> list = qhMapFilesImport.searchMapFiles(queryParams);
        String tableName = "qh_map_files";

        StringBuffer sql = new StringBuffer("select * from " + tableName + " ");

        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            whereSql.append(" and province like '%" + queryParams.getProvince() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            whereSql.append(" and city like '%" + queryParams.getCity() + "%'");
        } else {
            whereSql.append(" and (city IS NULL OR LENGTH(city)=0) ");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
            whereSql.append(" and area like '%" + queryParams.getArea() + "%'");
        } else {
            whereSql.append(" and (area IS NULL OR LENGTH(area)=0) ");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAffiliatedFileName())) {
            whereSql.append(" and affiliated_file_name ='" + queryParams.getAffiliatedFileName() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSave())) {
            whereSql.append(" and is_save ='" + queryParams.getIsSave() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsDelete())) {
            whereSql.append(" and is_delete = '" + queryParams.getIsDelete() + "'");
        }
        //上传类型（行政区划_）
        if (!PlatformObjectUtils.isEmpty(queryParams.getType())) {
            whereSql.append(" and type = '" + queryParams.getType() + "'");
        }
        //上传省市级类型（0：省，1：市）
        if (!PlatformObjectUtils.isEmpty(queryParams.getQualityStatus())) {
            whereSql.append(" and quality_status = '" + queryParams.getQualityStatus() + "'");
        }
        //上传日期
        if (!PlatformObjectUtils.isEmpty(queryParams.getDateStr())) {
            whereSql.append(" and date_str = '" + queryParams.getDateStr() + "'");
        }
        //查询
//        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), QhMapFilesEntity.class);
//        List<QhMapFilesEntity> list = query.getResultList();
        List<QhMapFilesEntity> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(QhMapFilesEntity.class));

        return list;
    }

    //查询所有未保存的文件
    @Override
    public List<QhMapFilesEntity> searchAllUnsaveMapFilesByJDBC() {
        StringBuilder sql = new StringBuilder("select * from qh_map_files ");
        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        whereSql.append(" and is_save = '0' and is_delete = '0' ");//未保存未删除
        whereSql.append(" order by create_time desc ");//时间降序排列
//        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), QhMapFilesEntity.class);
//        List<QhMapFilesEntity> list = query.getResultList();
        List<QhMapFilesEntity> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(QhMapFilesEntity.class));

        return list;
    }

    @Override
    public List<QhMapFilesEntity> getSlopeFileByDivisionAndType(String province, String city, String county, String type) {
        String tableName = "qh_map_files";

        StringBuffer sql = new StringBuffer("select * from " + tableName + "  where is_delete='0' ");
        if (PlatformObjectUtils.isNotEmpty(province)) {
            sql.append(" and province like '%" + province + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(city)) {
            sql.append(" and city like '%" + city + "%'");
        }
        if (PlatformObjectUtils.isNotEmpty(county)) {
            sql.append(" and area like '%" + county + "%'");
        }
        //上传类型（行政区划_）
        if (PlatformObjectUtils.isNotEmpty(type)) {
            sql.append(" and type = '" + type + "'");
        }
        if (PlatformObjectUtils.isNotEmpty(province)) {
            sql.append(" order by province,city ");
        }
        if (PlatformObjectUtils.isNotEmpty(city)) {
            sql.append(" order by province,city,area ");
        }
        if (PlatformObjectUtils.isNotEmpty(county)) {
            sql.append(" order by province,city,area ");
        }
        //查询
//        Query query = this.em.createNativeQuery(sql.toString(), QhMapFilesEntity.class);
//        List<QhMapFilesEntity> list = query.getResultList();
        List<QhMapFilesEntity> list = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(QhMapFilesEntity.class));

        return list;
    }

    @Override
    public List<QhMapFilesEntity> searchMapFilesByJDBC1(QhMapFilesQueryParams queryParams) {
        //List<QhMapFilesEntity> list = qhMapFilesImport.searchMapFiles(queryParams);
        String tableName = "qh_map_files";

        StringBuffer sql = new StringBuffer("select * from " + tableName + " ");

        StringBuilder whereSql = new StringBuilder(" where 1=1 ");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvince())) {
            whereSql.append(" and province like '%" + queryParams.getProvince() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCity())) {
            whereSql.append(" and city like '%" + queryParams.getCity() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getArea())) {
            whereSql.append(" and area like '%" + queryParams.getArea() + "%'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getAffiliatedFileName())) {
            whereSql.append(" and affiliated_file_name ='" + queryParams.getAffiliatedFileName() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsSave())) {
            whereSql.append(" and is_save ='" + queryParams.getIsSave() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getIsDelete())) {
            whereSql.append(" and is_delete = '" + queryParams.getIsDelete() + "'");
        }
        //上传类型（行政区划_）
        if (!PlatformObjectUtils.isEmpty(queryParams.getType())) {
            whereSql.append(" and type = '" + queryParams.getType() + "'");
        }
        //上传省市级类型（0：省，1：市）
        if (!PlatformObjectUtils.isEmpty(queryParams.getQualityStatus())) {
            whereSql.append(" and quality_status = '" + queryParams.getQualityStatus() + "'");
        }
        //上传日期
        if (!PlatformObjectUtils.isEmpty(queryParams.getDateStr())) {
            whereSql.append(" and date_str = '" + queryParams.getDateStr() + "'");
        }
        whereSql.append(" order by province,city,area");
        //查询
//        Query query = this.em.createNativeQuery(sql.append(whereSql).toString(), QhMapFilesEntity.class);
//        List<QhMapFilesEntity> list = query.getResultList();
        List<QhMapFilesEntity> list = jdbcTemplate.query(sql.append(whereSql).toString(), new BeanPropertyRowMapper<>(QhMapFilesEntity.class));

        return list;
    }
}
